﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;

namespace CommunitySystem
{
    public class CiZhiTuiXiuBO
    {
        public ObservableCollection<CiZhiModel> GetCiZhiModel(string orgid,int id)
        {
            ObservableCollection<CiZhiModel> rights = new ObservableCollection<CiZhiModel>();
            string sql = "";
            sql = string.Format(@"select row_number()over(order by cz.id ) Sec, cz.ID,cz.EmpbasicID,cz.Status,cz.WenHao,cz.YuanYin,cz.PiZhunDate,o.Name OrgName,o.ID OrgID,emp.Name,emp.PositionName,ed.DutyNo 
from CiZhi cz inner join Emp_BasicInfo emp on cz.EmpbasicID = emp.ID
inner join OrgForHR o on o.ID = emp.OrgID
inner join Emp_Duty ed on ed.EmpbasicID = emp.ID and ed.JobStatus = '在任'
where 1=1");
            if (orgid != "0" && !string.IsNullOrEmpty(orgid)) sql += " and o.ID in (" + orgid + ")";
            if (id != 0) sql += " and cz.id = " + id;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(CiZhiModel.FillEntity(dr));
            }
            return rights;
        }

        public bool InsertCiZhi(CiZhiModel model)
        {
                string sql = "";
                sql = string.Format(@"INSERT INTO CiZhi
           ([EmpbasicID]
           ,[WenHao]
           ,[PiZhunDate]
           ,[YuanYin]
           ,[Status],orgid)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,{4},{5})", model.EmpbasicID, model.WenHao, model.PiZhunDate, model.YuanYin, 0, model.OrgID);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                return true;
        }

        public bool UpdateCiZhi(CiZhiModel model)
        {
            string sql = "";
            sql = string.Format(@"UPDATE CiZhi
   SET [EmpbasicID] = {0}
      ,[WenHao] = '{1}'
      ,[PiZhunDate] = '{2}'
      ,[YuanYin] = '{3}'
 WHERE id = {4}", model.EmpbasicID, model.WenHao, model.PiZhunDate, model.YuanYin, model.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeleteCiZhi(int id)
        {
            string sql = "";
            sql = string.Format(@"delete from cizhi where id = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateCiZhiStatus(int id,DateTime? dt)
        {
            string sql = "";
            sql = string.Format(@"INSERT INTO CheGai
           ([EmpID] ,[OldZhiJi] ,[Status] ,[Demo] ,[RelationID],KeyID,[type],CreateDate,NewJinE,ChangeDate)
select (select EmpbasicID from CiZhi where ID = {0}),ed.dutylevel,'未导出','辞职',(select EmpbasicID from CiZhi where ID = {0}),
case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type=' 变动表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = (select EmpbasicID from CiZhi where ID = {0}))) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = (select EmpbasicID from CiZhi where ID = {0}))) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,'变动表',getdate(),0,'{1}'
 from  dbo.Emp_BasicInfo emp
inner join orgforhr o on emp.orgid = o.id
     left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任'
      where emp.ID = (select EmpbasicID from CiZhi where ID = {0});
UPDATE CiZhi
   SET status = 1
 WHERE id = {0};update Emp_BasicInfo set EmpStatus = '辞去公职',StatusDate = '{1}' where ID = 
(select EmpbasicID from CiZhi where ID = {0});
update Emp_Duty set JobStatus = '不在任' where EmpbasicID =(select EmpbasicID from CiZhi where ID = {0});
update Emp_Resume set enddate ='{1}'  where id = 
                                 (select top 1 id from dbo.Emp_Resume where empbasicid = (select EmpbasicID from CiZhi where ID = {0}) order by begindate desc);
", id, dt);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }




        public ObservableCollection<TuiXiuModel> GetTuiXiuModel(string orgid, int id)
        {
            ObservableCollection<TuiXiuModel> rights = new ObservableCollection<TuiXiuModel>();
            string sql = "";
            sql = string.Format(@"select row_number()over(order by cz.id ) Sec, cz.ID,cz.EmpbasicID,cz.Status,cz.WenHao,cz.YuanYin,cz.PiZhunDate,o.Name OrgName,o.ID OrgID,emp.Name,emp.PositionName,ed.DutyNo 
from TuiXiu cz inner join Emp_BasicInfo emp on cz.EmpbasicID = emp.ID
inner join OrgForHR o on o.ID = emp.OrgID
inner join Emp_Duty ed on ed.EmpbasicID = emp.ID and ed.JobStatus = '在任'
where 1=1");
            if (orgid != "0" && !string.IsNullOrEmpty(orgid)) sql += " and o.ID in (" + orgid + ")";
            if (id != 0) sql += " and cz.id = " + id;
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(TuiXiuModel.FillEntity(dr));
            }
            return rights;
        }

        public bool InsertTuiXiu(TuiXiuModel model)
        {
            string sql = "";
            sql = string.Format(@"INSERT INTO TuiXiu
           ([EmpbasicID]
           ,[WenHao]
           ,[PiZhunDate]
           ,[YuanYin]
           ,[Status],orgid)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,{4},{5})", model.EmpbasicID, model.WenHao, model.PiZhunDate, model.YuanYin, 0,model.OrgID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateTuiXiu(TuiXiuModel model)
        {
            string sql = "";
            sql = string.Format(@"UPDATE TuiXiu
   SET [EmpbasicID] = {0}
      ,[WenHao] = '{1}'
      ,[PiZhunDate] = '{2}'
      ,[YuanYin] = '{3}'
 WHERE id = {4}", model.EmpbasicID, model.WenHao, model.PiZhunDate, model.YuanYin, model.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool DeleteTuiXiu(int id)
        {
            string sql = "";
            sql = string.Format(@"delete from TuiXiu where id = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateTuiXiuStatus(int id, DateTime? dt)
        {
            string sql = "";
            sql = string.Format(@"INSERT INTO CheGai
           ([EmpID] ,[OldZhiJi] ,[Status] ,[Demo] ,[RelationID],KeyID,[type],CreateDate,NewJinE,ChangeDate)
select (select EmpbasicID from CiZhi where ID = {0}),ed.dutylevel,'未导出','退休',(select EmpbasicID from CiZhi where ID = {0}),
case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type=' 变动表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = (select EmpbasicID from CiZhi where ID = {0}))) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = (select EmpbasicID from CiZhi where ID = {0}))) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,'变动表',getdate(),0,'{1}'
 from  dbo.Emp_BasicInfo emp
inner join orgforhr o on emp.orgid = o.id
     left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任'
      where emp.ID = (select EmpbasicID from CiZhi where ID = {0});UPDATE TuiXiu
   SET status = 1
 WHERE id = {0};update Emp_BasicInfo set EmpStatus = '退休',StatusDate = '{1}' where ID = 
(select EmpbasicID from TuiXiu where ID = {0});
update Emp_Duty set JobStatus = '不在任' where EmpbasicID =(select EmpbasicID from TuiXiu where ID = {0});
update Emp_Resume set enddate ='{1}'  where id = 
                                 (select top 1 id from dbo.Emp_Resume where empbasicid = (select EmpbasicID from TuiXiu where ID = {0}) order by begindate desc);
", id, dt);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
    }
}
